﻿using System;
using System.Data.Common;
using System.Data.SqlClient;

namespace EF6PowerToolDemo
{
    /// <summary>
    /// 《Entity Framework 6 Recipes》中文翻译系列 (12) -----第三章 查询之使用SQL语句
    /// http://www.cnblogs.com/VolcanoCloud/p/4507099.html
    /// </summary>
    public class Class2
    {
        public static void Test()
        {
            // 删除之前的测试数据
            using (var context = new EFRecipesEntities())
            {
                context.Database.ExecuteSqlCommand("delete from chapter3.payment");
            }

            //插入两行数据
            using (var context = new EFRecipesEntities())
            {
                var sql = @"insert into Chapter3.Payment(Amount, Vendor)
                   values (@Amount, @Vendor)";    //这里可以使用@p0这样的参数占位符，ado.net为自动为我们创建参数对象

                var parameters = new DbParameter[]
                {
                    new SqlParameter { ParameterName = "Amount", Value = 99.97M },
                    new SqlParameter { ParameterName = "Vendor", Value = "Ace Plumbing" }
                };

                var rowCount = context.Database.ExecuteSqlCommand(sql, parameters);

                parameters = new DbParameter[]
                {
                    new SqlParameter { ParameterName = "Amount", Value = 43.83M },
                    new SqlParameter { ParameterName = "Vendor", Value = "Joe's Trash Service" }
                };

                rowCount += context.Database.ExecuteSqlCommand(sql, parameters);

                Console.WriteLine("{0} rows inserted", rowCount.ToString());
            }

            // 获取并显示数据
            using (var context = new EFRecipesEntities())
            {
                Console.WriteLine("Payments");
                Console.WriteLine("========================");
                foreach (var payment in context.Payments)
                {
                    Console.WriteLine("Paid {0} to {1}", payment.Amount.ToString(),
                                      payment.Vendor);
                }
            }

            Console.WriteLine("\nPress <enter> to continue...");
            Console.ReadLine();

        }
    }
}